from django.http import FileResponse
from django.shortcuts import render
from front import models
from django.shortcuts import HttpResponse
import json
from django.views.decorators.csrf import csrf_exempt
import sqlite3
import xlrd
from utils import restful
import datetime,time
import time
import os
import json
from shutil import copyfile
from apscheduler.scheduler import Scheduler

def index(request):
    return render(request, "index.html")


@csrf_exempt
def upload_file(request):
    uploadFileObj = request.FILES['myFile']
    uploadFileName = "dataResource/" + uploadFileObj.name
    MScode = request.POST.get("types")
    print("MScode: "+MScode)
    print(uploadFileName)
    with open(uploadFileName ,'wb') as saveFile:
        for chunk in uploadFileObj.chunks():
            saveFile.write(chunk)
    return restful.result(message="upload success", data={"MScode": MScode, "uploadFileName": uploadFileObj.name})


# 初始化sched模块的scheduler类
# 第一个参数是一个可以返回时间戳的函数，第二个参数可以在定时未到达之前阻塞。
sched = Scheduler()  # 实例化，固定格式
@sched.interval_schedule(seconds=5)  # 装饰器，seconds=60意思为该函数为1分钟运行一次
def mytask():
    execute_command()
sched.start()  # 启动该脚本

# 被周期性调度触发的函数
def execute_command():
    print('每10分钟执行一次')
    backupsql()    # 数据库备份
    rootdir = 'dataResource'
    list = os.listdir(rootdir)
    if len(list)==0:
        print('不存在文件')
    else:
        print('存在文件')
        # 列出文件夹下所有的目录与文件
        for i in range(0, len(list)):
            path = os.path.join(rootdir, list[i])
            if os.path.isfile(path):
                f = open(path, encoding='utf-8')
                data_json = json.load(f)
                code = 0
                sequenceNum = []
                data = xlrd.open_workbook('shandongdata.xlsx', encoding_override='utf-8')  # 读取表
                for dataj in data_json:
                    DisasterType, sqNumber = TransData(dataj["Code"])  # 解码ID，返回后七位的灾情信息码中的大类代码和子类代码
                    code = DisasterType
                    sequenceNum.append(sqNumber)
                    dataj["ReportingUnit"] = "202-" + dataj["ReportingUnit"]  # 将编码MSCode加在reportingunit字段中
                    disInfo = dataj["Code"]  # 获取19位编码
                    dataj["Location"] = TransDataAddress(data, disInfo[
                                                               0:12])  #  获取19位ID的前十二位编码，即基础地理信息码  # 获取19位ID的前十二位编码，即基础地理信息码
                    print("DisasterType:" + DisasterType)
                    print("data:" + str(dataj))
                databaseOperation(code, data_json, sequenceNum)
                f.close()
                os.remove(path)
                print('ok')


def backupsql():
    BACK_DIR = 'backupsql'
    # 如果目录不存在，新建目录
    if not os.path.exists(BACK_DIR):
        os.makedirs(BACK_DIR)
    conn = sqlite3.connect("db.sqlite3")
    # 备份中保留三个最新时间的数据，超时半小时的旧数据淘汰
    #db1.sql.bak对应最新的数据
    BACK_DIR1 =BACK_DIR + '/db1.sql.bak'
    BACK_DIR2 = BACK_DIR + '/db2.sql.bak'
    BACK_DIR3 = BACK_DIR + '/db3.sql.bak'

    if not os.path.exists(BACK_DIR1):
        with open(BACK_DIR1, "wb+") as f:
            for line in conn.iterdump():
                data = line + '\n'
                data = data.encode("utf-8")
                f.write(data)
    elif not os.path.exists(BACK_DIR2):
        #copyfile(source_file, destination_file)
        with open(BACK_DIR2, "wb+") as f:
            copyfile(BACK_DIR1,BACK_DIR2)
        with open(BACK_DIR1, "wb+") as f:
            for line in conn.iterdump():
                data = line + '\n'
                data = data.encode("utf-8")
                f.write(data)
    elif not os.path.exists(BACK_DIR3):
        with open(BACK_DIR3, "wb+") as f:
            copyfile(BACK_DIR2,BACK_DIR3)
        copyfile(BACK_DIR1, BACK_DIR2)
        with open(BACK_DIR1, "wb+") as f:
            for line in conn.iterdump():
                data = line + '\n'
                data = data.encode("utf-8")
                f.write(data)
    else:
        copyfile(BACK_DIR2, BACK_DIR3)
        copyfile(BACK_DIR1, BACK_DIR2)
        with open(BACK_DIR1, "wb+") as f:
            for line in conn.iterdump():
                data = line + '\n'
                data = data.encode("utf-8")
                f.write(data)

# 解析19位灾情信息编码
def TransData(IDNUmber):
    if(CheckIDNumber(IDNUmber)):
        disInfo=IDNUmber[-7:] #获取灾情信息编码，从倒数第七个字符到结尾
        sequenceNumber = disInfo[-4:]
        fdisInfo=disInfo[0] #获取大类代码
        sdisInfo=disInfo[1:3] #获取子类代码
        print('灾情信息编码：'+disInfo+' 大类代码：'+fdisInfo+' 子类代码：'+sdisInfo)
        if(fdisInfo=='1'and sdisInfo=='11'):
            dbTableNmae="DeathStatistics"  # 人员失踪及死亡：死亡
            dbTableType=fdisInfo+sdisInfo
        elif(fdisInfo=='2'and sdisInfo=='21'):
            dbTableNmae="CivilStructure"  # 房屋破坏：土木
            dbTableType = fdisInfo + sdisInfo
        elif(fdisInfo=='3'and sdisInfo=='36'):
            dbTableNmae="CommDisaster"  # 生命线工程灾情：通信
            dbTableType = fdisInfo + sdisInfo
        elif(fdisInfo=='4'and sdisInfo=='41'):
            dbTableNmae="CollapseRecord"    # 次生灾害：崩塌
            dbTableType = fdisInfo + sdisInfo
        elif(fdisInfo=='5'and sdisInfo=='52'):
            dbTableNmae="DisasterPrediction"  # 震情：灾情预测
            dbTableType = fdisInfo + sdisInfo
        else:
            dbTableNmae="000"
            dbTableType=fdisInfo+sdisInfo
        print(dbTableNmae+dbTableType)
        return dbTableType,sequenceNumber# 返回灾情信息编码的大类代码+子类代码


# 解析19位ID编码的前12位，得到具体的地理位置信息
def TransDataAddress(data,address_code):
    table = data.sheets()[0]  # 选定表
    nrows = table.nrows  # 获取行号
    # print('共有'+str(nrows)+'行数据')
    address_info = '地址'
    for i in range(1, nrows):  # 第0行为表头
        alldata = table.row_values(i)  # 循环输出excel表中每一行，即所有数据
        if (alldata[9] == address_code):
            # print(alldata[9])
            result = alldata[10] + alldata[11] + alldata[12] + alldata[13] + alldata[14]
            # print('查到的地址为：'+result)
            address_info = result
            # print('address_info:'+address_info)
            break
        else:
            address_info = '未找到'

    return address_info


@csrf_exempt
def test_url(request):
    print("test_url")
    result = request.POST.get("data1")
    return HttpResponse(result)


#数据表操作和文件的存储
def databaseOperation(code, datas,sequenceNumber):
    # 找到解析的336编码，不同编码不同目录
    if (code == '336'):
        fileName = '336/CommDisaster.json'
    if (code == '111'):
        fileName = '111/DeathStatistics.json'
    if (code == '221'):
        fileName = '221/CivilStructure.json'
    if (code == '441'):
        fileName = '441/CollapseRecord.json'
    if (code == '552'):
        fileName = '552/DisasterPrediction.json'

    # 为了方便操作，每次运行，，我都先清空数据表了
    conn = sqlite3.connect("db.sqlite3")
    cur = conn.cursor()
    table_name = "front_commdisaster"
    # sql = "Delete From {0}".format(table_name)
    # cur.execute(sql)
    # cur.execute("UPDATE sqlite_sequence set seq=0 where name='MSHD_commdisaster';")
    # conn.commit()
    # 将数据存入数据库
    i=0
    for data in datas:
        models.CommDisaster.objects.create(Code=data["Code"], Date=data["Date"], Location=data["Location"],
                                           Type=data["Type"], Grade=data["Grade"], Picture=data["Picture"],
                                           Note=data["Note"], ReportingUnit=data["ReportingUnit"],
                                           SequenceNumber=sequenceNumber[i])
        i=i+1
    # 将字典封装成json文件
    json_file = json.dumps(datas)
    # 保存json文件
    with open(fileName, 'w') as file_obj:
        json.dump(json_file, file_obj)
    return datas


# 测试数据是否是json格式，如果是json格式则抛出异常
def test_is_json(myjson):
    try:
        json.loads(myjson)
    except ValueError:
        return False
    return True


# 测试数据源的ID是否为19位编码
def test_source_data_ID_number(ID_number):
    if(len(ID_number)==19):
        return True
    else:
        return False


# 检查ID是否为19位编码
def CheckIDNumber(IDNumber):
    if(len(IDNumber)==19):
        return True
    else:
        return False

tableMap = {"331":"front_trafficdisaster",
            "332":"front_waterdisaster",
            "333":"front_oildisaster",
            "334":"front_gasdisaster",
            "335": "front_powerdisaster",
            "336": "front_commdisaster",
            "337": "front_irrigationdisaster"
          }
#"+str(tableMap[table_name])+"
#global table_name
table_name="111111"
#将json文件传到前段html文件中
def data(request):
    if request.method == "GET":
        list = request.path_info.split('/')
        global table_name
        #print(list)
        if list[2]=="nothing happened":
            return
        table_name=list[2]
        conn = sqlite3.connect("db.sqlite3")
        cur = conn.cursor()
        sql = "select * from "+str(tableMap[table_name])  # MSHD_commdisaster为表名
        cur.execute(sql)
        datas = cur.fetchall()  # 搜取所有结果
        cur.close()
        conn.close()
        input_type="button"
        submit_value="修改"
        BACKGROUND_COLOR="BACKGROUND-COLOR: transparent;color:whitesmoke;border:0;font-size: 16px;"
        return render(request, 'data.html',
                      {'items': datas, 'type': list[2],'input_type':input_type,
                       'submit_value':submit_value,'BACKGROUND_COLOR':BACKGROUND_COLOR})
        #return render(request, 'data.html', {'items': datas, 'type': list[2]})
    if request.method == "POST" and request.POST.get("submit") == "删除":
        id = request.POST.get("select")
        conn = sqlite3.connect("db.sqlite3")
        cur = conn.cursor()
        sql = "delete  from "+str(tableMap[table_name])+" where id=" + id  # MSHD_commdisaster为表名
        cur.execute(sql)
        sql2 = "select * from "+str(tableMap[table_name])
        cur.execute(sql2)
        datas = cur.fetchall()  # 搜取所有结果
        conn.commit()
        cur.close()
        conn.close()
        input_type = "button"
        submit_value = "修改"
        BACKGROUND_COLOR = "BACKGROUND-COLOR: transparent;color:whitesmoke;border:0;font-size: 16px;"
        return render(request, 'data.html',
                      {'items': datas, 'type': table_name, 'input_type': input_type, 'submit_value': submit_value,'BACKGROUND_COLOR':BACKGROUND_COLOR})
        #return render(request, 'data.html', {'items': datas,'type': table_name,'input_type':input_type})
        #return render(request, 'data.html', {'items': datas,'type': table_name})
    if request.method == "POST" and request.POST.get("submit") == "修改":
        conn = sqlite3.connect("db.sqlite3")
        cur = conn.cursor()
        sql = "select * from "+str(tableMap[table_name])  # MSHD_commdisaster为表名
        cur.execute(sql)
        datas = cur.fetchall()  # 搜取所有结果
        cur.close()
        conn.close()
        input_type = "text"
        submit_value = "提交修改"
        BACKGROUND_COLOR=""
        return render(request, 'data.html',
                      {'items': datas, 'type': table_name, 'input_type': input_type, 'submit_value': submit_value,'BACKGROUND_COLOR':BACKGROUND_COLOR})
    if request.method == "POST" and request.POST.get("submit") == "提交修改":
        #location = request.POST.get("23")
        conn = sqlite3.connect("db.sqlite3")
        cur = conn.cursor()
        sql = "select * from "+str(tableMap[table_name])
        cur.execute(sql)
        datas = cur.fetchall()  # 搜取所有结果
        for data in datas:
            location = "\"" + request.POST.get("fixLocation"+str(data[0])) + "\""
            grade = request.POST.get("fixGrade"+str(data[0]))
            date=request.POST.get("fixDate"+str(data[0]))
            sql1 = "UPDATE "+str(tableMap[table_name])+" SET Location=" + str(location) + ",Grade=" + str(
                grade) +",Date=" + str(date) + " WHERE id = " + str(data[0])
            #print(sql1)
            cur.execute(sql1)
        cur.execute(sql)
        datas = cur.fetchall()  # 搜取所有结果
        conn.commit()
        cur.close()
        conn.close()
        input_type = "button"
        submit_value = "修改"
        BACKGROUND_COLOR = "BACKGROUND-COLOR: transparent;color:whitesmoke;border:0;font-size: 16px;"
        return render(request, 'data.html',
                      {'items': datas, 'type':table_name, 'input_type': input_type, 'submit_value': submit_value,'BACKGROUND_COLOR':BACKGROUND_COLOR})
        #return render(request, 'data.html', {'items': datas, 'type': table_name, 'input_type': input_type})
        #return render(request, 'data.html', {'items': datas,'type': table_name})


# 用户访问 /request 网页并传入 code disasterType  o_URL requestunit
# ex: http://localhost:8000/request/?code=3700000000003366789&disasterType=336&o_url=168.336.222&requestUnit=水星水星
def data_request(request):
    # 获取问号后传递的参数
    print("data_request")
    code = request.POST.get("code")
    disaster_type = request.POST.get("disasterType")
    o_url = request.POST.get("o_url")
    request_unit = request.POST.get("requestUnit")
    # 获取当前时间，以年-月-日-时-分格式显示
    now_time = datetime.datetime.now()
    str_time = now_time.strftime("%Y-%m-%d %X")
    tup_time = time.strptime(str_time, "%Y-%m-%d %X")
    time_str = str(tup_time.tm_year) + "-" + str(tup_time.tm_mon) +"-"+str(tup_time.tm_mday)+" "+ str(tup_time.tm_hour) + ":" +str(tup_time.tm_min)
    # 将记录写入数据库
    models.requestList.objects.create(code=code, disasterType=disaster_type,
                                      status=0, o_URL=o_url,requestunit=request_unit,
                                      date=time_str)

    return restful.result(message="request success")


# 选择查看已发送还是未发送
def status(request):
    conn = sqlite3.connect("db.sqlite3")
    cur = conn.cursor()
    sql_word = "select * from front_requestList where status = 1 "  # 显示全部数据

    cur.execute(sql_word)
    request_data = cur.fetchall()  # 搜取所有结果
    cur.close()
    conn.close()

    # 进行筛选，已发送数据、未发送数据、发送数据
    if request.POST:
        flag = 0
        status = request.POST.get('status',None)
        sendId = request.POST.get('send',None)

        if (status == "已处理"):   # 已发送数据
            sql_word = "select * from front_requestList where status = 1 "
            conn = sqlite3.connect("db.sqlite3")
            cur = conn.cursor()
            cur.execute(sql_word)
            request_data = cur.fetchall()  # 搜取所有结果
            cur.close()
            conn.close()
            response = HttpResponse(json.dumps(request_data))
            flag = 0

        if (status == "未处理"):  # 未发送数据
            sql_word = "select * from front_requestList where status = 0 "
            conn = sqlite3.connect("db.sqlite3")
            cur = conn.cursor()
            cur.execute(sql_word)
            request_data = cur.fetchall()  # 搜取所有结果
            cur.close()
            conn.close()
            response = HttpResponse(json.dumps(request_data))
            flag=1

        if(sendId != None):  #发送数据，更改status的值
            sql_word1 = "update front_requestList set status = 1 where id= {0} ".format(sendId)  # 更新数据
            conn = sqlite3.connect("db.sqlite3")
            cur = conn.cursor()
            cur.execute(sql_word1)
            conn.commit()
            sql_word2 = "select * from front_requestList where status = 0 "  # 显示剩余的status = 0的数据
            cur.execute(sql_word2)
            request_data = cur.fetchall()  # 搜取所有结果
            flag = 1
            # 向指定url发送数据
            # 获得记录的url以及disasterType
            sql_word="select * from front_requestList where id={0}".format(sendId)
            cur.execute(sql_word)
            statusData = cur.fetchall()
            o_url=statusData[0][5]
            disaster_type=statusData[0][3]
            code=statusData[0][1]


            table_name=''
            # 获取要发送的数据
            if (disaster_type == '336'):
                table_name = "front_commdisaster"
            if (disaster_type == '111'):
                print("暂时没有这个表")
            if (disaster_type == '221'):
                print("暂时没有这个表")
            if (disaster_type == '441'):
                print("暂时没有这个表")
            if (disaster_type == '552'):
                print("暂时没有这个表")

            sql = "select * from {} where code = {}".format(table_name,code)
            print(sql)
            cur.execute(sql)
            send_data = cur.fetchall()  # 搜取所有结果
            cur.close()
            conn.close()
            # 将获取到的数据转换为json格式
            if len(send_data)==0:
                return restful.result(message="查询失败，数据库无此数据！")
            print(send_data)
            send_dic = {
                'code':send_data[0][1],'date':send_data[0][2],
                'location': send_data[0][3], 'type': send_data[0][4],
                'sequenceNumber': send_data[0][5], 'grade': send_data[0][6],
                'picture': send_data[0][7], 'note': send_data[0][8],
                'reportingUnit': send_data[0][9]
            }
            send_data = json.dumps(send_dic,ensure_ascii=False,indent=4)
            # 创建url专属文件夹
            curPath = os.getcwd()
            targetPath = curPath+os.path.sep+'sendResource'+os.path.sep+str(o_url)
            print(targetPath)
            if not os.path.exists(targetPath):
                os.makedirs(targetPath)
            file_name="{}.json".format(table_name)
            filePath=targetPath+os.path.sep+file_name
            with open(filePath, 'w') as f:
                f.write(send_data)
            f.close()
            print("写入成功")

    return render(request,'status.html', {'items': request_data,'flag':flag})

# 下载使用说明书
def download_instructions(request):
    file = open('static/file/instructions.pdf', 'rb')
    response = FileResponse(file)
    response['Content-Type'] = 'application/octet-stream'
    response['Content-Disposition'] = 'attachment;filename="instructions.pdf"'
    return response
